package top.majuan.study.study.dao;

import top.majuan.study.study.entity.TbNews;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbNewsDAO {
    public static final String INSERT = "insert into tb_news(title,content,source) values(?,?,?)";
    public static final String QUERY = "select id,title,source,hits,create_time from tb_news  order by id desc";
    public static final String QUERY_BY_ID = "select * from tb_news where id=?";
    public static final String DELETE = "delete from tb_news where id=?";
    public static final String UPDATE = "update tb_news set title=?,content=?,source=? where id=?";

    public static int update(TbNews news) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        ps.setString(1, news.getTitle());
        ps.setString(2, news.getContent());
        ps.setString(3, news.getSource());
        ps.setInt(4, news.getId());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }
    public static int delete(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(DELETE);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }
    public static TbNews queryById(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_ID);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        TbNews news = null;
       if (rs.next()) {
           news = new TbNews();
            news.setId(rs.getInt("id"));
            news.setTitle(rs.getString("title"));
            news.setContent(rs.getString("content"));
            news.setSource(rs.getString("source"));
            news.setCreate_time(rs.getDate("create_time"));
            news.setHits(rs.getInt("hits"));
        }
        conn.close();
        return news;
    }

    public static List<TbNews> query() throws Exception {
        List<TbNews> list = new ArrayList<>();
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TbNews news = new TbNews();
            news.setId(rs.getInt("id"));
            news.setTitle(rs.getString("title"));
//            news.setContent(rs.getString("content"));
            news.setSource(rs.getString("source"));
            news.setCreate_time(rs.getDate("create_time"));
            news.setHits(rs.getInt("hits"));
            list.add(news);
        }
        conn.close();
        return list;
    }

    public static int insert(TbNews news) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setString(1, news.getTitle());
        ps.setString(2, news.getContent());
        ps.setString(3, news.getSource());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }
}
